package org.yoao.moneymachine.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.moneymachine.bean.Bulletin;

/**
 * @author  Weishion.Yi
 * @description
 * @date 2010-10-4 下午03:12:13
 */
public class BulletinDAO extends DBManager {
	private static final Logger log = Logger.getLogger(BulletinDAO.class);

	public int getDataCount(Map map) throws Exception{
		String sql = "select count(id) as bulletinCount from bulletin";
		String where = " where ";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + key + "='" + value + "' and ";
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals(" where ")) where = "";
		sql += where;
		String result = querySingleValue(sql);
		if(result == null || result.trim().equals("")) result = "0";
		return Integer.parseInt(result);
	}


	public List find(int startRow, int endRow, Map map) throws Exception{
		String sql = "select top "+endRow+" bulletin.id, bulletin.title, bulletin.content, bulletin.createTime " +
					 "from bulletin " +
					 "where bulletin.id not in(";
		String filterSql = "select top "+startRow+" b2.id from bulletin as b2 ";

		String where = "";
		String fwhere = "";
		if(map != null && !map.isEmpty()){
			Iterator iter = map.keySet().iterator();
			while(iter.hasNext()){
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + "bulletin." + key + "='" + value + "' and ";
				fwhere = fwhere + "b2." + key + "='" + value + "' and ";
			}
		}
		if(where.endsWith(" and ")) where = where.substring(0, where.length()-4);
		if(where.equals("")) where = null;
		if(fwhere.endsWith(" and ")) fwhere = fwhere.substring(0, fwhere.length()-4);
		if(fwhere.equals("")) fwhere = null;

		if(where == null){
			fwhere = " order by b2.id desc";
			sql = sql + filterSql + fwhere + ") order by bulletin.id desc";
		}else{
			filterSql = filterSql + " where " + fwhere + " order by b2.id desc";
			sql = sql + filterSql + ") and " + where + " order by bulletin.id desc";
		}
		return find(sql);
	}

	public List findAll() throws Exception{
		String sql = "select id, title, content, createTime from bulletin";
		return find(sql);
	}

	public Bulletin findById(String id) throws Exception{
		String sql = "select id, title, content, createTime from bulletin where id="+id;
		return findSingle(querySingleRow(sql));
	}

	private List find(String sql) throws Exception{
		List list = queryMultiColumn(sql);
		List result = null;
		if(list != null && !list.isEmpty()){
			result = new ArrayList();
			for(int i=0;i<list.size();i++){
				Map map = (Map)list.get(i);
				Bulletin bulletin = findSingle(map);
				if(bulletin == null) bulletin = new Bulletin();
				result.add(bulletin);
			}
		}
		return result;
	}

	private Bulletin findSingle(Map data) throws Exception{
		Bulletin bulletin = null;
		if(data != null && !data.isEmpty()){
			bulletin = new Bulletin();
			bulletin.setId(data.get("ID").toString());
			bulletin.setTitle((String)data.get("TITLE"));
			bulletin.setContent((String)data.get("CONTENT"));
			bulletin.setCreateTime((String)data.get("CREATETIME"));
		}
		return bulletin;
	}

	public int insert(Bulletin bulletin) throws Exception{
		String sql = "insert into bulletin(title, content, createTime)values('"+
					  bulletin.getTitle()+"', '"+bulletin.getContent()+"', '"+bulletin.getCreateTime()+"')";
		return update(sql);
	}

	public int delete(String id) throws Exception{
		String sql = null;
		if(id.indexOf(",") > 0)
			sql = "delete from bulletin where id in("+id+")";
		else
			sql = "delete from bulletin where id="+id;
		return update(sql);
	}
}